<?php
registerTable("manager_linked", new table_manager_linked());

class table_manager_linked extends Database
{

    public function __construct()
    {
        parent::__construct("manager_linked");
    }

    public function fetch_class_data_by_uid($uid)
    {
        $sql = <<<END
        SELECT `manager_classes`.*, `manager_student`.*
        FROM `manager_classes`
        	, `manager_student`
        WHERE `manager_classes`.`classid` = `manager_student`.`classid` AND `manager_student`.`uid` = $uid;
        END;
        return $this->fetch_assoc_by_sql($sql);
    }

    public function fetch_teachers_by_classid($classid)
    {
        $sql = <<<END
        SELECT `manager_teacher_class`.*, `user_profile`.*, `manager_teacher_class`.`uid`, `manager_teacher`.*, `manager_teacher`.`sid`, `manager_subjects`.*
        FROM `manager_teacher_class`
        	, `user_profile`
        	, `manager_teacher`
        	, `manager_subjects`
        WHERE `manager_teacher_class`.`classid` = $classid AND `manager_teacher_class`.`uid` = `user_profile`.`uid` AND `manager_teacher_class`.`uid` = `manager_teacher`.`uid` AND `manager_teacher`.`sid` = `manager_subjects`.`sid`;
        END;
        return $this->fetch_all_assoc_by_sql($sql);
    }

    public function fetch_students_by_classid($id)
    {
        $sql = <<<END
        SELECT `manager_student`.*, `user_profile`.*
        FROM `manager_student`
        	, `user_profile`
        WHERE `manager_student`.`uid` = `user_profile`.`uid` AND `manager_student`.`classid` = $id;
        END;
        return $this->fetch_all_assoc_by_sql($sql);
    }

    public function count_child($uid)
    {
        $sql = <<<END
        SELECT `parents_relationship`.`childid`, `manager_student`.`uid`
        FROM `parents_relationship`
        	, `manager_student`
        WHERE `parents_relationship`.`childid` = `manager_student`.`uid` AND `parents_relationship`.`uid` = $uid;
        END;
        $this->query($sql);
        return $this->getRows();
    }

    public function fetch_master_by_uid_and_master($uid, $master)
    {
        $sql = <<<END
        SELECT `manager_student`.`uid`, `manager_classes`.`classid`, `manager_classes`.`master`
        FROM `manager_student`
        	, `manager_classes`
        WHERE `manager_student`.`uid` = $uid AND `manager_classes`.`master` = $master;
        END;
        $this->query($sql);
        return $this->getRows();
    }

    public function fetch_teacher_classes($uid)
    {
        $sql = <<<END
        SELECT `manager_classes`.*, `manager_teacher_class`.`classid`, `manager_teacher_class`.`uid`
        FROM `manager_classes`
        , `manager_teacher_class`
        WHERE `manager_teacher_class`.`uid` = $uid;
        END;
        return $this->fetch_all_assoc_by_sql($sql);
    }

    public function fetch_classes_by_uid($uid)
    {
        $sql = <<<END
        SELECT `manager_teacher_class`.`uid`, `manager_classes`.`classid`, `manager_classes`.*
        FROM `manager_teacher_class`
        	, `manager_classes`
        WHERE `manager_teacher_class`.`uid` = $uid AND `manager_classes`.`classid` = `manager_teacher_class`.`classid`;
        END;
        return $this->fetch_all_assoc_by_sql($sql);
    }

    public function fetch_classes_by_parent_uid($uid)
    {
        $sql = <<<END
         SELECT `parents_relationship`.`childid`, `manager_classes`.`classid`, `manager_student`.`uid`, `manager_classes`.*
         FROM `parents_relationship`
         	, `manager_classes`
         	, `manager_student`
         WHERE `parents_relationship`.`uid` = $uid AND `parents_relationship`.`childid` = `manager_student`.`uid` AND `manager_classes`.`classid` = `manager_student`.`classid`;
        END;
        return $this->fetch_all_assoc_by_sql($sql);
    }

    public function fetch_teacher_subject($uid)
    {
        $sql = <<<END
        SELECT `manager_teacher`.*, `manager_subjects`.`displayname`
        FROM `manager_teacher`
        , `manager_subjects`
        WHERE `manager_teacher`.`uid` = $uid AND `manager_subjects`.`sid` = `manager_teacher`.`sid`;
        END;
        return $this->fetch_assoc_by_sql($sql);
    }

    public function fetch_class_by_student($uid)
    {
        $sql = <<<END
        SELECT `manager_student`.`uid`, `manager_classes`.*
        FROM `manager_student`
        , `manager_classes`
        WHERE `manager_student`.`uid` = $uid AND `manager_classes`.`classid` = `manager_student`.`classid`;
        END;
        return $this->fetch_assoc_by_sql($sql);
    }

    public function fetch_activity_by_classid($classid, $offset, $amount)
    {
        $sql = <<<END
        SELECT * FROM
        ((SELECT
            `home_activity`.*
        FROM
            `manager_student`,
            `home_activity`
        WHERE
            `manager_student`.`classid` = $classid AND `home_activity`.`uid` = `manager_student`.`uid` AND `home_activity`.`view_level` = 0 GROUP BY `home_activity`.`aid` ORDER BY `home_activity`.`date` DESC
        	) UNION ALL(
        SELECT `home_activity`.*
        FROM `manager_teacher_class`
        	, `home_activity` 
        WHERE `manager_teacher_class`.`classid` = $classid AND `home_activity`.`uid` = `manager_teacher_class`.`uid` AND `home_activity`.`view_level` = 0 GROUP BY `home_activity`.`aid` ORDER BY `home_activity`.`date` DESC
        	)UNION ALL(
        SELECT `home_activity`.*
        FROM `manager_student`
        	, `parents_relationship`
        	, `home_activity`
        WHERE `manager_student`.`classid` = $classid AND `parents_relationship`.`childid` = `manager_student`.`uid` AND `home_activity`.`view_level` = 0 AND `home_activity`.`uid` = `parents_relationship`.`uid` GROUP BY `home_activity`.`aid` ORDER BY `home_activity`.`date` DESC)) 
        as total GROUP BY `total`.`aid` 
        ORDER BY `total`.`date` DESC
        LIMIT $offset,$amount
        END;
        return $this->fetch_all_assoc_by_sql($sql);
    }

    function fetch_sid_by_classid($classid)
    {
        $sql = <<<END
        SELECT `manager_teacher_class`.`classid`, `manager_teacher`.`uid`, `manager_teacher`.`sid`
        FROM `manager_teacher_class`
        	, `manager_teacher`
        WHERE `manager_teacher_class`.`classid` = '$classid' AND `manager_teacher`.`uid` = `manager_teacher_class`.`uid`;
        END;
        return $this->fetch_all_assoc_by_sql($sql);
    }
}
?>